/** <a href="http://www.cpupk.com/decompiler">Eclipse Class Decompiler</a> plugin, Copyright (c) 2017 Chen Chao. **/
package com.minierp.dao.hibernate;

import com.minierp.bean.UserBean;
import com.minierp.dao.FinanceDetailDao;
import com.minierp.dao.hibernate.BaseDaoImpl;
import com.minierp.helper.EncCommonHelper;
import java.math.BigDecimal;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;
import org.hibernate.HibernateException;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;

public class FinanceDetailDaoImpl extends BaseDaoImpl
		implements
			FinanceDetailDao {
	public int delete(final long billId, final String billType) {
		Object obj = this.getHibernateTemplate().execute(
				new HibernateCallback() {
					public Object doInHibernate(Session session)
							throws HibernateException, SQLException {
						String hql = "delete com.minierp.model.FinanceDetail where billId = :billId and billType=:billType";
						return new Integer(session.createQuery(hql)
								.setLong("billId", billId)
								.setString("billType", billType)
								.executeUpdate());
					}
				});
		return ((Integer) obj).intValue();
	}

	public int estCorporate(final long fromEstId, final long toEstId) {
		Object obj = this.getHibernateTemplate().execute(
				new HibernateCallback() {
					public Object doInHibernate(Session session)
							throws HibernateException, SQLException {
						String hql = "update com.minierp.model.FinanceDetail set estId="
								+ toEstId + " where estId=" + fromEstId;
						return new Integer(session.createQuery(hql)
								.executeUpdate());
					}
				});
		return ((Integer) obj).intValue();
	}

	public List<?> getEstBackCountTotal(Date startDate, Date endDate,
			long estId, long areaId, long etId, long elId, long mngId,
			long tsId, UserBean ub, BigDecimal minCost, BigDecimal maxCost)
			throws Exception {
		String str = this.getEstBackCountSql(startDate, endDate, estId, areaId,
				etId, elId, mngId, tsId, ub);
		String tblStr = " finance_detail b,establishment e";
		if (areaId != 0L) {
			tblStr = tblStr + ",area a";
		}

		String whereStr = "";
		if (minCost != null) {
			whereStr = " where cost>=" + minCost + " and cost<" + maxCost;
		}

		String sql = "select count(*),sum(imp_cost),sum(rec_cost),sum(cost) from (select sum(imp_cost) as imp_cost,sum(rec_cost) as rec_cost,sum(imp_cost+rec_cost) as cost,est_id from ( select e.id as est_id,b.rp_cost as imp_cost,0 as rec_cost from"
				+ tblStr
				+ " where b.est_id=e.id"
				+ " and b.rp_cost!=0"
				+ " and b.bill_type=\'BUSINESS_BILL\'"
				+ str
				+ " union all "
				+ " select e.id as est_id,0 as imp_cost,b.rp_cost as rec_cost from"
				+ tblStr
				+ " where b.est_id=e.id"
				+ " and b.bill_type=\'RECEIVE_BILL\'"
				+ str
				+ ") aa group by aa.est_id" + ") aaa" + whereStr;
		Session sess = null;

		List arg24;
		try {
			sess = this.getSession();
			SQLQuery q = sess.createSQLQuery(sql);
			this.setEstBusinessCountPara(q, startDate, endDate);
			arg24 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg24;
	}

	public List<?> getEstBackCountList(Date startDate, Date endDate,
			long estId, long areaId, long etId, long elId, long mngId,
			long tsId, UserBean ub, BigDecimal minCost, BigDecimal maxCost,
			int startIndex, int num) throws Exception {
		String str = this.getEstBackCountSql(startDate, endDate, estId, areaId,
				etId, elId, mngId, tsId, ub);
		String tblStr = " finance_detail b,establishment e";
		if (areaId != 0L) {
			tblStr = tblStr + ",area a";
		}

		String whereStr = "";
		if (minCost != null) {
			whereStr = " and aaa.cost>=" + minCost + " and aaa.cost<" + maxCost;
		}

		String sql = "select aaa.imp_cost,aaa.rec_cost,aaa.cost,ee.id,ee.est_name,ee.area_id,ee.est_type_id,ee.est_level_id,ee.manager_name,ee.linkman,ee.linkman_phone from (select sum(imp_cost) as imp_cost,sum(rec_cost) as rec_cost,sum(imp_cost+rec_cost) as cost,est_id from ( select e.id as est_id,b.rp_cost as imp_cost,0 as rec_cost from"
				+ tblStr
				+ " where b.est_id=e.id"
				+ " and b.rp_cost!=0"
				+ " and b.bill_type=\'BUSINESS_BILL\'"
				+ str
				+ " union all "
				+ " select e.id as est_id,0 as imp_cost,b.rp_cost as rec_cost from"
				+ tblStr
				+ " where b.est_id=e.id"
				+ " and b.bill_type=\'RECEIVE_BILL\'"
				+ str
				+ ") aa group by aa.est_id"
				+ ") aaa,establishment ee where aaa.est_id=ee.id"
				+ whereStr
				+ " order by aaa.cost desc";
		Session sess = null;

		List arg26;
		try {
			sess = this.getSession();
			SQLQuery q = sess.createSQLQuery(sql);
			this.setEstBusinessCountPara(q, startDate, endDate);
			q.setFirstResult(startIndex);
			q.setMaxResults(num);
			arg26 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg26;
	}

	private String getEstBackCountSql(Date startDate, Date endDate, long estId,
			long areaId, long etId, long elId, long mngId, long tsId,
			UserBean ub) {
		String str = "";
		if (startDate != null) {
			str = str + " and b.bill_date>=:startDate";
		}

		if (endDate != null) {
			str = str + " and b.bill_date<:endDate";
		}

		if (estId != 0L) {
			str = str + " and e.id=" + estId;
		}

		if (areaId != 0L) {
			str = str + " and a.id=e.area_id and (a.id=" + areaId
					+ " or (a.parent_id_str like \'%;" + areaId + ";%\'))";
		}

		if (etId != 0L) {
			str = str + " and e.est_type_id=" + etId;
		}

		if (elId != 0L) {
			str = str + " and e.est_level_id=" + elId;
		}

		if (tsId != 0L) {
			str = str + " and b.tally_set_id=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("b.tally_set_id",
							ub, true);
		}

		if (mngId != 0L) {
			str = str + " and e.manager_id=" + mngId;
		}

		return str;
	}

	private void setEstBusinessCountPara(SQLQuery q, Date startDate,
			Date endDate) {
		if (startDate != null) {
			q.setDate("startDate", startDate);
		}

		if (endDate != null) {
			q.setDate("endDate", endDate);
		}

	}

	public List getAppEstBackCounTota(Date startDate, Date endDate,
			String estName, long tsId, UserBean ub, long mngId)
			throws Exception {
		String str = "";
		if (startDate != null) {
			str = str + " and b.bill_date>=:startDate";
		}

		if (endDate != null) {
			str = str + " and b.bill_date<:endDate";
		}

		if (estName != null && !estName.equals("")) {
			str = str + " and (e.est_name like :estName)";
		}

		if (mngId != 0L) {
			str = str + " and e.manager_id=" + mngId;
		}

		if (tsId != 0L) {
			str = str + " and b.tally_set_id=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("b.tally_set_id",
							ub, true);
		}

		String sql = "select count(*),sum(cost),sum(imp_cost),sum(rec_cost) from (select sum(a.imp_cost) as imp_cost,sum(a.rec_cost) as rec_cost,sum(a.imp_cost+a.rec_cost) as cost,a.est_id from ( select e.id as est_id,b.rp_cost as imp_cost,0 as rec_cost from finance_detail b,establishment e where b.est_id=e.id and b.rp_cost!=0 and b.bill_type=\'BUSINESS_BILL\'"
				+ str
				+ " union all "
				+ " select e.id as est_id,0 as imp_cost,b.rp_cost as rec_cost from"
				+ " finance_detail b,establishment e"
				+ " where b.est_id=e.id"
				+ " and b.bill_type=\'RECEIVE_BILL\'"
				+ str
				+ ") a group by a.est_id" + ") aa";
		Session sess = null;

		List arg13;
		try {
			sess = this.getSession();
			SQLQuery q = sess.createSQLQuery(sql);
			if (startDate != null) {
				q.setDate("startDate", startDate);
			}

			if (endDate != null) {
				q.setDate("endDate", endDate);
			}

			if (estName != null && !estName.equals("")) {
				q.setString("estName", "%" + estName + "%");
			}

			arg13 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg13;
	}

	public List getAppEstBackCoun(Date startDate, Date endDate, String estName,
			long tsId, UserBean ub, long mngId, int startIndex, int num)
			throws Exception {
		String str = "";
		if (startDate != null) {
			str = str + " and b.bill_date>=:startDate";
		}

		if (endDate != null) {
			str = str + " and b.bill_date<:endDate";
		}

		if (estName != null && !estName.equals("")) {
			str = str + " and (e.est_name like :estName)";
		}

		if (mngId != 0L) {
			str = str + " and e.manager_id=" + mngId;
		}

		if (tsId != 0L) {
			str = str + " and b.tally_set_id=" + tsId;
		} else {
			str = str
					+ EncCommonHelper.createTallySetSQLStr("b.tally_set_id",
							ub, true);
		}

		String sql = "select ee.est_name,aa.cost,aa.imp_cost,aa.rec_cost,ee.manager_name,ee.area_id,ee.id from (select sum(a.imp_cost) as imp_cost,sum(a.rec_cost) as rec_cost,sum(a.imp_cost+a.rec_cost) as cost,a.est_id from ( select e.id as est_id,b.rp_cost as imp_cost,0 as rec_cost from finance_detail b,establishment e where b.est_id=e.id and b.rp_cost!=0 and b.bill_type=\'BUSINESS_BILL\'"
				+ str
				+ " union all "
				+ " select e.id as est_id,0 as imp_cost,b.rp_cost as rec_cost from"
				+ " finance_detail b,establishment e"
				+ " where b.est_id=e.id"
				+ " and b.bill_type=\'RECEIVE_BILL\'"
				+ str
				+ ") a group by a.est_id"
				+ ") aa,establishment ee where aa.est_id=ee.id"
				+ " order by aa.cost desc";
		Session sess = null;

		List arg15;
		try {
			sess = this.getSession();
			SQLQuery q = sess.createSQLQuery(sql);
			if (startDate != null) {
				q.setDate("startDate", startDate);
			}

			if (endDate != null) {
				q.setDate("endDate", endDate);
			}

			if (estName != null && !estName.equals("")) {
				q.setString("estName", "%" + estName + "%");
			}

			q.setFirstResult(startIndex);
			q.setMaxResults(num);
			arg15 = q.list();
		} finally {
			if (sess != null) {
				this.releaseSession(sess);
			}

		}

		return arg15;
	}
}